package com.example.carborrow.dao;


import com.example.carborrow.model.Car;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Repository
public class CarDao {

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public CarDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }



    //获取所有车
    private final  static String GET_ALL_CARS_SQL ="SELECT * FROM car";
    public List<Car> getAllCars() {

        return jdbcTemplate.query(GET_ALL_CARS_SQL,new CarRowMapper());
    }

    //获取所有车 排序
    private final  static String GET_ALL_CARS_ORDER_BY_STATUS_SQL ="SELECT * FROM car ORDER BY CASE WHEN status = 1 THEN 0 ELSE 1  END ,status";
    public List<Car> getAllCarsOrderBySTATUS() {
        return jdbcTemplate.query(GET_ALL_CARS_ORDER_BY_STATUS_SQL,new CarRowMapper());
    }

    //按品牌或车型查询
    private final  static String GET_CARS_BY_BM_SQL ="SELECT * FROM car where carBrand like ? or carModel like ?";
    public List<Car> getCarsByBM(String searchWord) {
        String sw = "%" +searchWord+"%";
        return jdbcTemplate.query(GET_CARS_BY_BM_SQL,new CarRowMapper(),sw,sw);
    }

    //按品牌或车型查询 排序
    private final  static String GET_CARS_BY_BM_ORDER_BY_STATUS_SQL ="SELECT * FROM car where carBrand like ? or carModel like ? ORDER BY CASE WHEN status = 1 THEN 0 ELSE 1  END ,status";

    public List<Car> getCarsByBMOrderByStatus(String searchWord) {
        String sw = "%" +searchWord+"%";
        return jdbcTemplate.query(GET_CARS_BY_BM_ORDER_BY_STATUS_SQL,new CarRowMapper(),sw,sw);
    }


    //按车借出状态筛选（getAll）
    private final  static String GET_CARS_BY_STATUS_SQL ="SELECT * FROM car where status=?";
    public List<Car> getCarsByStatus(int status) {
        return jdbcTemplate.query(GET_CARS_BY_STATUS_SQL,new CarRowMapper(),status);
    }



    //按车牌号查询
    private final static String GET_CAR_BY_LP_SQL = "SELECT * FROM car WHERE licensePlate = ?";
    public Car getCarByLicensePlate(String licensePlate) {

        return jdbcTemplate.queryForObject(GET_CAR_BY_LP_SQL, new CarRowMapper(), licensePlate);
    }


    // 新增汽车
    private final static String ADD_CAR_SQL = "INSERT INTO car (carBrand,carModel, licensePlate, color, price,status) VALUES (?, ?, ?, ?, ?,?)";
    public int addCar(String carBrand , String carModel , String licensePlate , String color , String price  , int status ){
        return jdbcTemplate.update(ADD_CAR_SQL, carBrand, carModel, licensePlate, color, price,status);
    }


    // 更新汽车信息
    private final static String UPDATE_CAR_SQL ="UPDATE car SET carbrand=?, carModel=?, licensePlate = ?, color = ?,price = ?, status = ?  WHERE carId = ?";
    public int updateCar(int carId , String carBrand , String carModel , String licensePlate , String color , BigDecimal price  , int status ){
       return jdbcTemplate.update(UPDATE_CAR_SQL, carBrand, carModel, licensePlate, color, price,status, carId);
    }

    // 更新汽车状态
    private final static String UPDATE_CAR_STATUS_SQL ="UPDATE car SET status = ? WHERE carId = ?";
    public int updateCarStatus(int status,int carId) {
        return jdbcTemplate.update(UPDATE_CAR_STATUS_SQL, status,carId);
    }


    // 删除汽车
    private final static String DEL_CAR_SQL="DELETE FROM car WHERE carId = ?";
    public int deleteCar(int carId) {

        return jdbcTemplate.update(DEL_CAR_SQL, carId);
    }


    private static class CarRowMapper implements RowMapper<Car> {
        @Override
        public Car mapRow(ResultSet rs, int rowNum) throws SQLException {
            Car car = new Car();
            car.setCarId(rs.getInt("carId"));
            car.setCarBrand(rs.getString("carBrand"));
            car.setCarModel(rs.getString("carModel"));
            car.setLicensePlate(rs.getString("licensePlate"));
            car.setColor(rs.getString("color"));
            car.setStatus(rs.getInt("status"));
            car.setPrice(rs.getBigDecimal("price"));
            return car;
        }
    }
}
